if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SearchStaffWithPaging]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SearchStaffWithPaging]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




/*
	version: 1.0
	by: MAC
	date: 2008/03/13
	description:
	
	Initial creation
*/

/*
	version: 1.1
	by: MAC
	date: 2008/03/18
	description:
	
	Added filter for location id

*/

/*
	version: 1.2
	by: MAC
	date: 2008/04/19
	description:
	
	Explicility defined columns so that expensive photo column is not brought back

*/

CREATE         PROCEDURE SearchStaffWithPaging
	(
		@StartRowIndex INT = 1, 
		@MaximumRows INT = 10,
		@LastName VARCHAR(512) = NULL,
		@FirstName VARCHAR(512) = NULL,
		@LocationId INT = -1
	)
AS
		SET NOCOUNT ON

		DECLARE @FirstId INT
	
		IF @StartRowIndex < 1
			SET @StartRowIndex = 1


		-- A check can be added to make sure @startRowIndex isn't > count(1)
		-- from employees before doing any actual work unless it is guaranteed
		-- the caller won't do that
		
		-- Get the first employeeID for our page of records
		SET ROWCOUNT @StartRowIndex
		SELECT @FirstId = [Id] 
							FROM Staff s
							WHERE (s.LastName LIKE @LastName OR @LastName IS NULL)
							AND (s.FirstName LIKE @FirstName OR @FirstName IS NULL)
							AND (s.LocationId = @LocationId OR @LocationId = -1)
							ORDER BY [Id]
	
		
		-- Now, set the row count to MaximumRows and get
		-- all records >= @first_id
		SET ROWCOUNT @MaximumRows
		
		SELECT 
			s.[Id],
			s.LastName,
			s.MiddleName,
			s.FirstName,
			s.EmailAddress,
			s.TelephoneNumber,
			s.Extension,
			s.LocationId,
			null as Photo
		FROM Staff s
		WHERE [Id] >= @FirstId
		AND (s.LastName LIKE @LastName OR @LastName IS NULL)
		AND (s.FirstName LIKE @FirstName OR @FirstName IS NULL)
		AND (s.LocationId = @LocationId OR @LocationId = -1)
		ORDER BY [Id]
		
		SET ROWCOUNT 0
		
		SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

